<?php

class Raport extends Controller {

    function Raport() {
        parent::Controller();
        $this->load->model("model_user");
        $this->load->model("model_raport");
        $this->load->model("model_data");
        $this->load->library('xmlexcel');
    }


    function bringRaport1() {

        $dFrom = $this->input->post("from");
        $dEnd = $this->input->post("end");
        $iUserId = $this->input->post("user");

        if ($iUserId == "") {
            $aResponse = array("error" => true, "description" => "Alegeti un user", "type" => "security");
            echo json_encode($aResponse);
            return;
        }

        $data["iRec"] = $iUserId;
        $aUserDetails = $this->model_user->getRecord($data);

        if (($dFrom == "") && ($dEnd == "")) {
            $dFrom = date("Y-m-d", strtotime(date('m') . '/01/' . date('Y')));
            $dEnd = date("Y-m-d", strtotime('-1 second', strtotime('+1 month', strtotime(date('m') . '/01/' . date('Y')))));
        }

        $this->session->set_userdata("raport_from", $dFrom);
        $this->session->set_userdata("raport_end", $dEnd);
        $this->session->set_userdata("raport_user", $iUserId);

        $chart = $this->InsertChart("charts.swf", "charts_library", "raport/clasament_user_chart/simple", 500, 450, "#FFFFFF", true);
        $chart_proc = $this->InsertChart("charts.swf", "charts_library", "raport/clasament_user_chart/proc", 350, 350, "#FFFFFF", true);

        echo $chart . $chart_proc;
    }


    function clasament_user_chart() {

        $begin_date = $this->session->userdata("raport_from");
        $end_date = $this->session->userdata("raport_end");
        $user_id = $this->session->userdata("raport_user");

        $typeChart = $this->uri->segment(3);

        if (($begin_date != '')and($end_date != '')) {
            $filtru_mod = " and ( date(date_modified) >= '" . $begin_date . "' and date(date_modified) <= '" . $end_date . "' )  ";
            $filtru_add = " and ( date(date_created) >= '" . $begin_date . "' and date(date_created) <= '" . $end_date . "' )  ";
            $filtru_tot = " and (( date(date_modified) >= '" . $begin_date . "' and date(date_modified) <= '" . $end_date . "' and modify_by='" . $user_id . "' ) or ( date(date_created) >= '" . $begin_date . "' and date(date_created) <= '" . $end_date . "' and create_by='" . $user_id . "' )) ";
        }

        if (($begin_date != '')and($end_date == '')) {
            $filtru_mod .= " and  date(date_modified) >= '" . $begin_date . "'  ";
            $filtru_add .= " and  date(date_created) >= '" . $begin_date . "'  ";
            $filtru_tot = " and ( date(date_modified) >= '" . $begin_date . "'  or  date(date_created) >= '" . $begin_date . "'  ) ";
        }

        if (($begin_date == '')and($end_date != '')) {
            $filtru_mod .= " and  date(date_modified) <= '" . $end_date . "'  ";
            $filtru_add .= " and  date(date_created) <= '" . $end_date . "'  ";
            $filtru_tot = " and ( date(date_modified) <= '" . $end_date . "'  or  date(date_created) <= '" . $end_date . "'  ) ";
        }


        $modificate = $this->model_raport->getNumerMod($filtru_mod, $user_id);
        $create = $this->model_raport->getNumerCreate($filtru_add, $user_id);
        $total = $this->model_raport->getNumerTotal($filtru_tot, $user_id);


        if ($typeChart == "simple") {

            $nume = array('', 'Modificate', 'Adaugate');
            $total = array('', $modificate, $create);
            $chart['axis_value'] = array('font' => "arial", 'bold' => true, 'size' => 10, 'color' => "000000", 'alpha' => 100, 'steps' => 7, 'decimals' => 0, 'separator' => "");

            $chart ['axis_category'] = array("bold" => true, 'orientation' => "diagonal_up", 'font' => "Arial", 'size' => 12,
                'color' => "000000");
            $chart['chart_border'] = array('color' => "000000", 'top_thickness' => 1, 'bottom_thickness' => 2, 'left_thickness' => 0, 'right_thickness' => 0);
            $chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
            $chart['chart_rect'] = array('x' => 75, 'y' => 50, 'width' => 400, 'height' => 300, 'positive_color' => "B9B7B7", 'positive_alpha' => 100);
            $chart['chart_transition'] = array('type' => "scale", 'delay' => .5, 'duration' => .5, 'order' => "series");
            $chart['chart_value'] = array('color' => "ffffff", 'alpha' => 100, 'font' => "arial", 'bold' => true, 'size' => 10, 'position' => "middle", 'decimals' => 0, 'separator' => "", 'as_percentage' => false);
            $chart ['chart_type'] = "column";
            $chart ['series_switch'] = true;
            $chart['legend_rect'] = array('x' => -1000, 'y' => -1000, 'width' => 1, 'height' => 1, 'margin' => 1);
            $chart['series_color'] = array("ddaa41", "88dd11", "4e62dd", "ff8811", "4d4d4d", "5a4b6e", '4ecc72', '846e54', 'd9db4c', 'db714c', '4cdbd6', 'db4cd6', 'a04cdb');
            $chart['series_gap'] = array('set_gap' => 50);
            $chart['chart_data'] = array($nume, $total);
        }


        if ($typeChart == "proc") {
            $nume = array('', 'Modificate', 'Adaugate');
            $total = array('', $modificate, $create);

            $chart['chart_data'] = array($nume, $total);
            $chart['chart_rect'] = array('positive_color' => "000000", 'positive_alpha' => 0);
            $chart['chart_type'] = "pie";
            $chart['chart_value'] = array('color' => "000000", 'alpha' => 100, 'font' => "arial", 'bold' => true, 'size' => 10, 'position' => "inside", 'prefix' => "", 'suffix' => "", 'decimals' => 0, 'separator' => "", 'as_percentage' => true);
            $chart['legend_label'] = array('layout' => "horizontal", 'bullet' => "circle", 'font' => "arial", 'bold' => true, 'size' => 10, 'color' => "000000", 'alpha' => 100);
            $chart['legend_rect'] = array('fill_color' => "ffffff", 'fill_alpha' => 0, 'line_color' => "000000", 'line_alpha' => 0, 'line_thickness' => 1);
            $chart['series_color'] = array("ddaa41", "88dd11", "4e62dd", "ff8811", "4d4d4d", "5a4b6e", '4ecc72', '846e54', 'd9db4c', 'db714c', '4cdbd6', 'db4cd6', 'a04cdb');
        }

        $this->SendChartData($chart);

    }


    function bringRaport2() {

        $dFrom = $this->input->post("from");
        $dEnd = $this->input->post("end");
        $sOpType = $this->input->post("type");

        if ($sOpType == "") $sOpType = "total";

        if (($dFrom == "") && ($dEnd == "")) {
            $dFrom = date("Y-m-d", strtotime(date('m') . '/01/' . date('Y')));
            $dEnd = date("Y-m-d", strtotime('-1 second', strtotime('+1 month', strtotime(date('m') . '/01/' . date('Y')))));
        }

        $this->session->set_userdata("raport_from", $dFrom);
        $this->session->set_userdata("raport_end", $dEnd);
        $this->session->set_userdata("raport_type", $sOpType);

        $chart = $this->InsertChart("charts.swf", "charts_library", "raport/clasament_chart/simple", 500, 550, "#FFFFFF", true);
        $chart_proc = $this->InsertChart("charts.swf", "charts_library", "raport/clasament_chart/proc", 350, 350, "#FFFFFF", true);
        echo $chart . $chart_proc;
    }

    function clasament_chart() {

        $aUsers = $this->model_user->getUserRaport();
        $begin_date = $this->session->userdata("raport_from");
        $end_date = $this->session->userdata("raport_end");
        $type = $this->session->userdata("raport_type");
        $typeChart = $this->uri->segment(3);

        foreach ($aUsers as $key => $value)
        {

            $filtru_mod = "";
            $filtru_add = "";
            $filtru_tot = "";

            $iUserId = $key;

            if (($begin_date != '')and($end_date != '')) {
                $filtru_mod = " and ( date(date_modified) >= '" . $begin_date . "' and date(date_modified) <= '" . $end_date . "' )  ";
                $filtru_add = " and ( date(date_created) >= '" . $begin_date . "' and date(date_created) <= '" . $end_date . "' )  ";
                $filtru_tot = " and (( date(date_modified) >= '" . $begin_date . "' and date(date_modified) <= '" . $end_date . "' and modify_by='" . $iUserId . "' ) or ( date(date_created) >= '" . $begin_date . "' and date(date_created) <= '" . $end_date . "' and create_by='" . $iUserId . "' )) ";
            }

            if (($begin_date != '')and($end_date == '')) {
                $filtru_mod .= " and  date(date_modified) >= '" . $begin_date . "'  ";
                $filtru_add .= " and  date(date_created) >= '" . $begin_date . "'  ";
                $filtru_tot = " and (( date(date_modified) >= '" . $begin_date . "'  and modify_by='" . $iUserId . "' ) or ( date(date_created) >= '" . $begin_date . "' and create_by='" . $iUserId . "' )) ";
            }

            if (($begin_date == '')and($end_date != '')) {
                $filtru_mod .= " and  date(date_modified) <= '" . $end_date . "'  ";
                $filtru_add .= " and  date(date_created) <= '" . $end_date . "'  ";
                $filtru_tot = " and ((date(date_modified) <= '" . $end_date . "' and modify_by='" . $iUserId . "' ) or (date(date_created) <= '" . $end_date . "' and create_by='" . $iUserId . "' )) ";
            }

            $modificate[$key] = $this->model_raport->getNumerMod($filtru_mod, $iUserId);
            $create[$key] = $this->model_raport->getNumerCreate($filtru_add, $iUserId);
            $total[$key] = $this->model_raport->getNumerTotal($filtru_tot, $iUserId);
        }


        foreach ($total as $tot)
            $tot_abs += $tot;

        foreach ($create as $tot_c)
            $tot_abs_c += $tot_c;

        foreach ($modificate as $tot_m)
            $tot_abs_m += $tot_m;

        $this->rnatsort($total);
        $this->rnatsort($create);
        $this->rnatsort($modificate);

        if ($type == 'total') {
            $arr_val = $total;
            $_SESSION['total'] = $tot_abs;
        }
        if ($type == 'add') {
            $arr_val = $create;
            $_SESSION['total'] = $tot_abs_c;
        }
        if ($type == 'mod') {
            $arr_val = $modificate;
            $_SESSION['total'] = $tot_abs_m;
        }

        $valori = array('-');
        $nume = array('-');

        foreach ($arr_val as $key => $value)
        {
            if ($value > 0) {
                $valori[] = $value;
                $nume[] = $aUsers[$key];
            }
        }

        $nume = array_values($nume);
        $valori = array_values($valori);


        if ($typeChart == "simple") {
            $chart['axis_value'] = array('font' => "arial", 'bold' => true, 'size' => 10, 'color' => "000000", 'alpha' => 100, 'steps' => 7, 'decimals' => 0, 'separator' => "");
            $chart ['axis_category'] = array("bold" => true, 'orientation' => "diagonal_up", 'font' => "Arial", 'size' => 12, 'color' => "000000");
            $chart['chart_border'] = array('color' => "000000", 'top_thickness' => 1, 'bottom_thickness' => 2, 'left_thickness' => 0, 'right_thickness' => 0);
            $chart['chart_grid_h'] = array('alpha' => 20, 'color' => "000000", 'thickness' => 1, 'type' => "dashed");
            $chart['chart_rect'] = array('x' => 75, 'y' => 50, 'width' => 400, 'height' => 400, 'positive_color' => "B9B7B7", 'positive_alpha' => 100);
            $chart['chart_transition'] = array('type' => "scale", 'delay' => .5, 'duration' => .5, 'order' => "series");
            $chart['chart_value'] = array('color' => "ffffff", 'alpha' => 100, 'font' => "arial", 'bold' => true, 'size' => 10, 'position' => "middle", 'decimals' => 0, 'separator' => "", 'as_percentage' => false);
            $chart ['chart_type'] = "column";
            $chart ['series_switch'] = true;
            $chart['legend_rect'] = array('x' => -1000, 'y' => -1000, 'width' => 1, 'height' => 1, 'margin' => 1);
            $chart['series_color'] = array("ddaa41", "88dd11", "4e62dd", "ff8811", "4d4d4d", "5a4b6e", '4ecc72', '846e54', 'd9db4c', 'db714c', '4cdbd6', 'db4cd6', 'a04cdb');
            $chart['series_gap'] = array('set_gap' => 50);
            $chart['chart_data'] = array($nume, $valori);
        }

        if ($typeChart == "proc") {
            $chart['chart_data'] = array($nume, $valori);
            $chart['chart_rect'] = array('positive_color' => "000000", 'positive_alpha' => 0);
            $chart['chart_type'] = "pie";
            $chart['chart_value'] = array('color' => "000000", 'alpha' => 100, 'font' => "arial", 'bold' => true, 'size' => 10, 'position' => "inside", 'prefix' => "", 'suffix' => "", 'decimals' => 0, 'separator' => "", 'as_percentage' => true);
            $chart['legend_label'] = array('layout' => "horizontal", 'bullet' => "circle", 'font' => "arial", 'bold' => true, 'size' => 10, 'color' => "000000", 'alpha' => 100);
            $chart['legend_rect'] = array('fill_color' => "ffffff", 'fill_alpha' => 0, 'line_color' => "000000", 'line_alpha' => 0, 'line_thickness' => 1);
            $chart['series_color'] = array("ddaa41", "88dd11", "4e62dd", "ff8811", "4d4d4d", "5a4b6e", '4ecc72', '846e54', 'd9db4c', 'db714c', '4cdbd6', 'db4cd6', 'a04cdb');
            //$chart[ 'series_explode' ] = array ( 20, 0, 50 );
        }

        $this->SendChartData($chart);

    }


    function InsertChart($flash_file, $library_path, $php_source, $width = 400, $height = 250, $bg_color = "666666", $transparent = false, $license = null) {

        $php_source = urlencode($php_source);
        $library_path = urlencode($library_path);
        $protocol = (strtolower($_SERVER['HTTPS']) != 'on') ? 'http' : 'https';

        $html = "<OBJECT classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' codebase='" . $protocol . "://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0' ";
        $html .= "width='" . $width . "' height='" . $height . "' id='charts' />";
        $u = (strpos($flash_file, "?") === false) ? "?" : ((substr($flash_file, -1) === "&") ? "" : "&");
        $html .= "<PARAM NAME='movie' VALUE='" . $flash_file . $u . "library_path=" . $library_path . "&stage_width=" . $width . "&stage_height=" . $height . "&php_source=" . $php_source;
        if ($license != null) {
            $html .= "&license=" . $license;
        }
        $html .= "' /> <PARAM NAME='quality' VALUE='high' /><param name='allowScriptAccess' value='sameDomain' /><PARAM NAME='bgcolor' VALUE='#" . $bg_color . "' /> ";
        if ($transparent) {
            $html .= "<PARAM NAME='wmode' VALUE='transparent' /> ";
        }
        $html .= "<EMBED src='" . $flash_file . $u . "library_path=" . $library_path . "&stage_width=" . $width . "&stage_height=" . $height . "&php_source=" . $php_source;
        if ($license != null) {
            $html .= "&license=" . $license;
        }
        $html .= "' quality='high' bgcolor='#" . $bg_color . "' width='" . $width . "' height='" . $height . "' NAME='charts' allowScriptAccess='sameDomain' swLiveConnect='true' ";
        if ($transparent) {
            $html .= "wmode=transparent ";
        } //use wmode=opaque to prevent printing on black
        $html .= "TYPE='application/x-shockwave-flash' PLUGINSPAGE='" . $protocol . "://www.macromedia.com/go/getflashplayer'></EMBED></OBJECT>";
        return $html;

    }

//====================================
    function SendChartData($chart = array()) {

        //header("Content-Type: text/xml");
        //header("Cache-Control: cache, must-revalidate");
        //header("Pragma: public");

        $xml = "<chart>\r\n";
        $Keys1 = array_keys((array) $chart);
        for ($i1 = 0; $i1 < count($Keys1); $i1++) {
            if (is_array($chart[$Keys1[$i1]])) {
                $Keys2 = array_keys($chart[$Keys1[$i1]]);
                if (is_array($chart[$Keys1[$i1]][$Keys2[0]])) {
                    $xml .= "\t<" . $Keys1[$i1] . ">\r\n";
                    for ($i2 = 0; $i2 < count($Keys2); $i2++) {
                        $Keys3 = array_keys((array) $chart[$Keys1[$i1]][$Keys2[$i2]]);
                        switch ($Keys1[$i1]) {
                            case "chart_data":
                                $xml .= "\t\t<row>\r\n";
                                for ($i3 = 0; $i3 < count($Keys3); $i3++) {
                                    switch (true) {
                                        case ($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] === null):
                                            $xml .= "\t\t\t<null/>\r\n";
                                            break;

                                        case ($Keys2[$i2] > 0 and $Keys3[$i3] > 0):
                                            $xml .= "\t\t\t<number>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</number>\r\n";
                                            break;

                                        default:
                                            $xml .= "\t\t\t<string>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</string>\r\n";
                                            break;
                                    }
                                }
                                $xml .= "\t\t</row>\r\n";
                                break;

                            case "chart_value_text":
                                $xml .= "\t\t<row>\r\n";
                                $count = 0;
                                for ($i3 = 0; $i3 < count($Keys3); $i3++) {
                                    if ($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] === null) {
                                        $xml .= "\t\t\t<null/>\r\n";
                                    }
                                    else {
                                        $xml .= "\t\t\t<string>" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "</string>\r\n";
                                    }
                                }
                                $xml .= "\t\t</row>\r\n";
                                break;

                            /*case "link_data_text":
                               $xml.="\t\t<row>\r\n";
                               $count=0;
                               for($i3=0;$i3<count($Keys3);$i3++){
                                   if($chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]]===null){$xml.="\t\t\t<null/>\r\n";}
                                   else{$xml.="\t\t\t<string>".$chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]]."</string>\r\n";}
                               }
                               $xml.="\t\t</row>\r\n";
                               break;*/

                            case "draw":
                                $text = "";
                                $xml .= "\t\t<" . $chart[$Keys1[$i1]][$Keys2[$i2]]['type'];
                                for ($i3 = 0; $i3 < count($Keys3); $i3++) {
                                    if ($Keys3[$i3] != "type") {
                                        if ($Keys3[$i3] == "text") {
                                            $text = $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]];
                                        }
                                        else {
                                            $xml .= " " . $Keys3[$i3] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "\"";
                                        }
                                    }
                                }
                                if ($text != "") {
                                    $xml .= ">" . $text . "</text>\r\n";
                                }
                                else {
                                    $xml .= " />\r\n";
                                }
                                break;


                            default: //link, etc.
                                $xml .= "\t\t<value";
                                for ($i3 = 0; $i3 < count($Keys3); $i3++) {
                                    $xml .= " " . $Keys3[$i3] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]][$Keys3[$i3]] . "\"";
                                }
                                $xml .= " />\r\n";
                                break;
                        }
                    }
                    $xml .= "\t</" . $Keys1[$i1] . ">\r\n";
                }
                else {
                    if ($Keys1[$i1] == "chart_type" or $Keys1[$i1] == "series_color" or $Keys1[$i1] == "series_image" or $Keys1[$i1] == "series_explode" or $Keys1[$i1] == "axis_value_text") {
                        $xml .= "\t<" . $Keys1[$i1] . ">\r\n";
                        for ($i2 = 0; $i2 < count($Keys2); $i2++) {
                            if ($chart[$Keys1[$i1]][$Keys2[$i2]] === null) {
                                $xml .= "\t\t<null/>\r\n";
                            }
                            else {
                                $xml .= "\t\t<value>" . $chart[$Keys1[$i1]][$Keys2[$i2]] . "</value>\r\n";
                            }
                        }
                        $xml .= "\t</" . $Keys1[$i1] . ">\r\n";
                    }
                    else { //axis_category, etc.
                        $xml .= "\t<" . $Keys1[$i1];
                        for ($i2 = 0; $i2 < count($Keys2); $i2++) {
                            $xml .= " " . $Keys2[$i2] . "=\"" . $chart[$Keys1[$i1]][$Keys2[$i2]] . "\"";
                        }
                        $xml .= " />\r\n";
                    }
                }
            }
            else { //chart type, etc.
                $xml .= "\t<" . $Keys1[$i1] . ">" . $chart[$Keys1[$i1]] . "</" . $Keys1[$i1] . ">\r\n";
            }
        }
        $xml .= "</chart>\r\n";
        echo $xml;
    }


    function rnatsort(&$a) {
        natsort($a);
        $a = array_reverse($a, true);
    }


    function dayContact() {

        $aResult =$this->db->query("
            SELECT DATE(date_modified2) as ziua,`name`, COUNT(person_id) as cnt
            FROM persons
            LEFT JOIN `user` on `user`.user_id=persons.modify_by2
            WHERE
                DATE(date_modified2) > DATE_SUB(NOW(),INTERVAL 6 MONTH)
                AND  priority != 4
            GROUP BY DATE(date_modified2), modify_by2
            ORDER BY date_modified2 DESC  ")->result_array();


        $this->load->library('xmlexcel');
        header("Content-type: application/vnd.ms-excel;charset=utf-8");
        header("Content-disposition:  attachment; filename=raport.xlsx");
        $newexcel = new Xmlexcel;

        $newexcel->write_header();
        $newexcel->write_document_properties('MTP');
        $newexcel->write_styles();
        $newexcel->start_worksheet();
        $newexcel->write_row('
		<Column ss:Width="200"/>
		<Column ss:Width="200"/>
		<Column ss:Width="250"/>
	    ');

        $newexcel->write_row('
	    <Row>
		<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Ziua</Data></Cell>
		<Cell ss:StyleID="headerStyle"><Data ss:Type="String">User</Data></Cell>
		<Cell ss:StyleID="headerStyle"><Data ss:Type="String">Numar contacte</Data></Cell>
	     </Row>');


        foreach ($aResult as $key => $row) {
            $newexcel->write_row('<Row>
		    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["ziua"] . '</Data></Cell>
		    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["name"] . '</Data></Cell>
		    <Cell ss:StyleID="defaultComp"><Data ss:Type="String">' . $row["cnt"] . '</Data></Cell>
		    </Row>');
        }

        $newexcel->end_worksheet();
        $newexcel->write_footer();
        print $newexcel->output();
        exit;
    }
}